package org.light.framework;

import java.sql.SQLException;
import java.util.ArrayList;

import org.light.framework.bean.PageArgs;
import org.light.framework.bean.ResultColumn;
import org.light.framework.bean.ResultOrder;
import org.light.framework.bean.SearchArgs;
import org.light.framework.dao.SQLHelper;
import org.light.framework.orm.DataTable;
import org.light.framework.orm.SQLLoger;
import org.light.framework.util.StringUtil;

/// <summary>
/// 数据表查询器
/// </summary>
public class DataTableMediator {

	/**
	 * 获取符合条件的所有记录
	 * 
	 * @param tableOrViewName
	 *            表名或视图名
	 * @param searchArgs
	 *            查询参数，可为null
	 * @param resultOrder
	 *            排序参数，可为null
	 * @param resultColumn
	 *            结果列，可为null
	 * @return
	 */
	public static DataTable getAll(String tableOrViewName, SearchArgs searchArgs, ResultOrder resultOrder,
			ResultColumn resultColumn) {
		if (searchArgs == null)
			searchArgs = new SearchArgs();
		if (resultOrder == null)
			resultOrder = new ResultOrder();
		if (resultColumn == null)
			resultColumn = new ResultColumn();

		String columns = resultColumn.toString();
		String where = searchArgs.toString();
		String order = resultOrder.toString();

		ArrayList<Object> listParams = searchArgs.getDataParameters();

		if (!StringUtil.isNullOrEmpty(order))
			order = " ORDER BY " + order;

		String sql = String.format("SELECT %s FROM %s %s %s", columns, tableOrViewName, where, order);
		SQLLoger.write(sql, listParams.toArray());

		DataTable dt = new DataTable();
		try {
			dt = SQLHelper.fillDataTable(sql, listParams.toArray());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dt;
	}

	/**
	 * 单 表或视图 分页查询，返回DataTable
	 * 
	 * @param tableOrViewName
	 *            表名或视图名
	 * @param searchArgs
	 *            查询参数，可为null
	 * @param pageArgs
	 *            分页参数，不能为null
	 * @param resultOrder
	 *            排序参数，不能为null
	 * @param resultColumn
	 *            查询结果列
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("null")
	public static DataTable search(String tableOrViewName, SearchArgs searchArgs, PageArgs pageArgs,
			ResultOrder resultOrder, ResultColumn resultColumn) throws Exception {
		if (searchArgs == null)
			searchArgs = new SearchArgs();
		if (resultOrder == null && resultOrder.IsEmpty())
			throw new Exception("排序参数 ResultOrder 不能为空");
		if (pageArgs == null)
			throw new Exception("分页参数 PageArgs 不能为空");

		String columns = resultColumn.toString();
		String whereSql = searchArgs.toString();
		String orderSql = resultOrder.toString();
		ArrayList<Object> listParams = searchArgs.getDataParameters();

		// 获取总记录条数
		String countSql = String.format("SELECT COUNT(*) FROM %s %s", tableOrViewName, whereSql);

		SQLLoger.write(countSql, listParams.toArray());

		try {
			Object count = SQLHelper.executeScalar(countSql, listParams.toArray());
			pageArgs.totalResults = StringUtil.toInt(count);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

		// 查询结果集
		String sql = String.format("SELECT %s FROM %s %s ORDER BY %s OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", columns,
				tableOrViewName, whereSql, orderSql, (pageArgs.pageIndex - 1) * pageArgs.pageSize, pageArgs.pageSize);

		SQLLoger.write(sql, listParams.toArray());

		DataTable dt = new DataTable();
		try {
			dt = SQLHelper.fillDataTable(sql, listParams.toArray());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dt;
	}

	/**
	 * 金额求和
	 * 
	 * @param tableOrViewName
	 * @param columnName
	 *            数据库列名
	 * @param searchArgs
	 *            查询参数
	 * @return
	 */
	public static Double sum(String tableOrViewName, String columnName, SearchArgs searchArgs) {
		if (searchArgs == null)
			searchArgs = new SearchArgs();

		String where = searchArgs.toString();
		ArrayList<Object> listParams = searchArgs.getDataParameters();

		String sql = String.format("SELECT SUM(%s) FROM %s %s", columnName, tableOrViewName, where);

		SQLLoger.write(sql, listParams.toArray());

		Object result = 0;
		try {
			result = SQLHelper.executeScalar(sql, listParams.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return StringUtil.toDouble(result);
	}

	/**
	 * 求总记录数
	 * 
	 * @param tableOrViewName
	 *            数据表名视图名
	 * @param searchArgs
	 *            查询参数<
	 * @return
	 */
	public static int count(String tableOrViewName, SearchArgs searchArgs) {
		if (searchArgs == null)
			searchArgs = new SearchArgs();

		String whereSql = searchArgs.toString();
		ArrayList<Object> listParams = searchArgs.getDataParameters();

		String sql = String.format("SELECT COUNT(*) FROM %s %s", tableOrViewName, whereSql);

		SQLLoger.write(sql, listParams.toArray());

		Object result = 0;
		try {
			result = SQLHelper.executeScalar(sql, listParams.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return StringUtil.toInt(result);

	}

}